﻿using System;
using System.Data.SqlClient;
using System.Windows;
using System.Data;
using System.Windows.Data;
using System.IO;
using System.Windows.Controls;
using System.Runtime.InteropServices;
using System.Collections.Generic;
using OfficeOpenXml;
using DocumentFormat.OpenXml.Office2016.Drawing.ChartDrawing;
using DocumentFormat.OpenXml.Drawing;
using System.Collections.ObjectModel;



namespace black_screen_mode_server
{
    /// <summary>
    /// FormQuery.xaml 的交互逻辑
    /// </summary>
    public partial class FormQuery : System.Windows.Window
    {


        private string connectionString = ProjectTools.configDbConnString;
        private int pageSize = 10;
        private ObservableCollection<string> pageItems = new ObservableCollection<string>();

        public FormQuery()
        {
            InitializeComponent();
            DataContext = this;
            this.cmPages.ItemsSource = pageItems;
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        }

        private void LoadReportData(int page)
        {
            DateTime? startTime = StartTime.Value;
            DateTime? endTime = EndTime.Value;

            if (startTime.HasValue && endTime.HasValue)
            {
                string query = $"select * from timing_report where end_time > convert(DATETIME, '{startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}') " +
                $"and end_time < convert(DATETIME, '{endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}') order by end_time " +
                $"OFFSET {page*pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY";
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    ReportDataGrid.Columns.Clear();

                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "序号", Binding = new Binding("id") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "起始时间", Binding = new Binding("start_time") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "结束时间", Binding = new Binding("end_time") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "持续时间", Binding = new Binding("duration") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "点位", Binding = new Binding("source") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "操作站名", Binding = new Binding("station") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "工序", Binding = new Binding("groupName") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "事件类型", Binding = new Binding("category_name") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "区域", Binding = new Binding("location_tag_name") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "优先级", Binding = new Binding("priority") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "事件描述", Binding = new Binding("condition_name") });
                    ReportDataGrid.Columns.Add(new DataGridTextColumn() { Header = "位号描述", Binding = new Binding("description") });

                    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
                    System.Data.DataTable dataTable = new System.Data.DataTable();
                    adapter.Fill(dataTable);
                    ReportDataGrid.ItemsSource = dataTable.DefaultView;
                     
                }

               
            }
            else
            {
                MessageBox.Show("请输入开始时间和结束时间", "提示");
                return;
            }
        }

        private void LoadDataPages(DateTime? startTime, DateTime? endTime)
        {
            try
            {
                string sql = $"select count(*) from timing_report where end_time > convert(DATETIME, '{startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}') " +
               $"and end_time < convert(DATETIME, '{endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}')";
                SqlHelper sqlhelper = new SqlHelper(ProjectTools.configDbConnString);
                int total = sqlhelper.ExecuteScalar(sql);
                int pages = total / pageSize + (total % pageSize == 0 ? 0 : 1);
                this.pageItems.Clear();
                for (int i = 1; i <= pages; i++)
                {
                    this.pageItems.Add("第"+i.ToString()+"页");
                }
            }
            catch (Exception)
            {

            }
           
            
        }

        private void OnQueryClick(object sender, RoutedEventArgs e)
        {
            LoadReportData(0);
            DateTime? startTime = StartTime.Value;
            DateTime? endTime = EndTime.Value;
            LoadDataPages(startTime, endTime);
            this.cmPages.SelectedIndex = 0;
        }

        //导出当前时间范围内的DataGrid内容

        private void btnExport_Click(object sender, RoutedEventArgs e)
        {
            DateTime? startTime = StartTime.Value;
            DateTime? endTime = EndTime.Value;

            if(!startTime.HasValue || !endTime.HasValue)
            {
                MessageBox.Show("请输入开始时间和结束时间", "提示");
                return;
            }
            string strStartTime = startTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            string strEndTime = endTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            var saveFileDialog = new Microsoft.Win32.SaveFileDialog();
            saveFileDialog.Filter = "Excel Files|*.xlsx";
            saveFileDialog.Title = "请选择保存目录";
            saveFileDialog.FileName = $"Report_{DateTime.Now.ToString("yyyyMMddhhmm")}";
            bool? result = saveFileDialog.ShowDialog();

            if (result == true)
            {
                try
                {
                    ProjectTools.ExportDataToExcel(saveFileDialog.FileName,strStartTime,strEndTime);
                    MessageBox.Show("导出成功", "提示");
                }
                catch (Exception)
                {
                    MessageBox.Show("导出失败", "提示");
                    
                }

                //try
                //{
                //    if (!string.IsNullOrEmpty(saveFileDialog.FileName))
                //    {
                //        FileInfo newFile = new FileInfo(saveFileDialog.FileName);
                //        using (ExcelPackage package = new ExcelPackage(newFile))
                //        {

                //            ExcelWorksheet worksheet1 = package.Workbook.Worksheets.Add("ReportData");

                //            for (int i = 0; i < ReportDataGrid.Columns.Count; i++)
                //            {
                //                worksheet1.Cells[2, i + 2].Value = ReportDataGrid.Columns[i].Header;
                //            }

                //            for (int i = 0; i < ReportDataGrid.Columns.Count; i++)
                //            {
                //                for (int j = 0; j < ReportDataGrid.Items.Count; j++)
                //                {
                //                    var cellContent = ReportDataGrid.Columns[i].GetCellContent(ReportDataGrid.Items[j]);
                //                    string cellText = (cellContent as TextBlock)?.Text;

                //                    worksheet1.Cells[j + 3, i + 2].Value = cellText;
                //                }
                //            }
                //            worksheet1.Cells.AutoFitColumns();


                //            ExcelWorksheet worksheet2 = package.Workbook.Worksheets.Add("SummaryData");
                           
                //            var summaryData= ProjectTools.GenerateSummarySql(ProjectTools.configDbConnString, strStartTime, strEndTime);

                //            worksheet2.Cells[1, 1].Value = "名称";
                //            worksheet2.Cells[1, 2].Value = "黑屏率(%)";
                //            worksheet2.Cells[1, 3].Value = "最佳时长";
                //            worksheet2.Cells[1, 4].Value = "开始时间";
                //            worksheet2.Cells[1, 5].Value = "结束时间";
                //            worksheet2.Column(2).Width = 10;
                //            worksheet2.Column(3).Width = 20;
                //            worksheet2.Column(4).Width = 20;
                //            worksheet2.Column(5).Width = 20;
                //            worksheet2.Column(6).Width = 20;
                //            if (summaryData != null)
                //            {
                //                for (int i = 0; i < summaryData.Count; i++)
                //                {
                //                    worksheet2.Cells[2 + i, 1].Value = summaryData[i].Name;
                //                    worksheet2.Cells[2 + i, 2].Value = summaryData[i].BlackScreenRate;
                //                    worksheet2.Cells[2 + i, 3].Value = summaryData[i].Duration;
                //                    worksheet2.Cells[2 + i, 4].Value = summaryData[i].StartTime;
                //                    worksheet2.Cells[2 + i, 5].Value = summaryData[i].EndTime;
                //                }
                //            }
                            
                //            package.Save();                            
                //        }
                //        MessageBox.Show("导出成功！", "提示");
                //    }
                //}
                //catch (Exception ex)
                //{
                //    MessageBox.Show("导出失败！" + ex, "提示");
                //}
            }
        }

        private void cmPages_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            LoadReportData(cmPages.SelectedIndex);
        }
    }
}
